<?php

class Torneo_Model extends CI_Model {

	var $title = '';
	var $content = '';
	var $date = '';

	function __construct() {
		// Call the Model constructor
		parent::__construct();
	}

	function get_tournaments_active() {
		
		$this -> db -> select('tournament.tournament_id, tournament.tournament_name, category.category_desc, category.category_sport ');
		$this -> db -> from('tournament');
		//$this -> db -> join('tournament', 'tournament.tournament_id = tournament_teams_rel.tournament_id');
		$this -> db -> join('category', 'category.category_id= tournament.category_id');

		$this -> db -> where('active_flag', TRUE);
		//$this -> db -> join()
		//$this -> db -> group_by('tournament.tournament_name');
		//$this -> db -> where('tournament_teams_rel.tournament_id = ' . $tournamentId);

		$query = $this -> db -> get();

		/*(foreach($query->result() as $row) {
		 echo "<table>";
		 echo "<tr>";
		 print_r($row);
		 echo "</tr>";
		 echo "</table>";
		 }*/

		return $query -> result();
	}

	function get_tournament_info($tournament_id) {

		$this -> db -> select('tournament_id, tournament_name, category_desc, category_sport, tournament_init_date, tournament_end_date');
		$this -> db -> from('tournament');
		$this -> db -> join('category', 'category.category_id = tournament.category_id');
		$this -> db -> where('tournament_id', $tournament_id);
		$query = $this -> db -> get();
		return $query -> result();
	}

	function get_matches_for_tournament($tournament) {

		$sql = "select match_id, b.team_name as team_home, c.team_name as team_visit, match_date, match_scored from matches a, teams b, teams c where a.team_home = b.team_id and a.team_visit = c.team_id and tournament_id= " . $tournament . " ";
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	function get_all_matches() {

		$sql = "select match_id, tournament.tournament_name, b.team_name as team_home , c.team_name as team_visit, match_date, match_scored from matches a, teams b, teams c, tournament where a.team_home = b.team_id and a.team_visit = c.team_id and tournament.tournament_id = a.tournament_id;";
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	function get_tournament_teams($tournament_id) {

		$this -> db -> select('teams.team_id, teams.team_name');
		$this -> db -> from('teams');
		$this -> db -> join('tournament_teams_rel', 'tournament_teams_rel.team_id = teams.team_id');
		$this -> db -> where('tournament_id', $tournament_id);
		$query = $this -> db -> get();
		return $query -> result();

	}

	function get_tournaments_active_with_limit() {

		$this -> db -> select('tournament.tournament_id, tournament.tournament_name, category.category_desc, category.category_sport ');
		$this -> db -> from('tournament');
		$this -> db -> join('category', 'category.category_id= tournament.category_id');
		$this -> db -> where('active_flag', TRUE);
		$this -> db -> limit(5);
		$query = $this -> db -> get();
		return $query -> result();

	}

	function get_match_info_for_edit($match_id) {

		$sql = "select match_id, tournament.tournament_name, b.team_id as team_home_id, b.team_name as team_home, c.team_id as team_visit_id ,c.team_name as team_visit,match_date,match_scored from matches a, teams b, teams c, tournament where a.team_home = b.team_id and a.team_visit = c.team_id and tournament.tournament_id = a.tournament_id and match_id = " . $match_id . "";
		$query = $this -> db -> query($sql);
		return $query -> result();

	}

	function get_players_in_team($team_id) {

		$sql = "SELECT a.team_id, player_number, a.player_id, player_name, city, birth_date from team_player_rel a , players b where a.player_id = b.player_id and team_id=" . $team_id;
		$query = $this -> db -> query($sql);
		return $query -> result();

	}

	function get_teams_on_tournament($tournamentId) {

		$this -> db -> select('teams.team_id, teams.team_name,tournament.tournament_name,tournament.category_id ');
		$this -> db -> from('tournament_teams_rel');
		$this -> db -> join('tournament', 'tournament.tournament_id = tournament_teams_rel.tournament_id');
		$this -> db -> join('teams', 'teams.team_id= tournament_teams_rel.team_id');
		$this -> db -> where('tournament_teams_rel.tournament_id = ' . $tournamentId);

		$query = $this -> db -> get();

		return $query -> result();
	}

	function get_score_by_match_id($match_id) {

		$sql = "select a.match_id, a.match_date, a.match_scored, a.team_home as team_home_id, c.team_name as team_home_name, a.team_visit as team_visit_id, d.team_name as team_visit_name, b.score as home_score, e.score as visit_score from teams c, teams d, matches a left join ( select player_score.team_id, match_id, sum(points) as score from player_score, teams where match_id= " . $match_id . " and teams.team_id = player_score.team_id group by player_score.team_id ) b on ( a.match_id = b.match_id and a.team_home = b.team_id ) left join ( select  player_score.team_id, match_id, sum(points) as score  from player_score, teams where match_id= " . $match_id . " and teams.team_id = player_score.team_id group by player_score.team_id ) e on ( a.match_id = e.match_id and a.team_visit = e.team_id ) where c.team_id = a.team_home and d.team_id = a.team_visit and a.match_id = " . $match_id . ";";
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	function get_annotations_by_match_id($match_id, $team_id) {
		$sql = "select b.player_id, a.score_id, a.team_id, b.player_name, a.points, a.match_id, a.time  from player_score a left join players b on  a.player_id = b.player_id where a.match_id = " . $match_id . " and a.team_id = " . $team_id . " order by a.time;";
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	/**
	 * TODO:
	 * Limitar a 5 y usar otro metodo para jalar mas partidos de la liga
	 * Condicionar a solo partidos terminados.
	 *
	 */
	function get_matches_by_tournament_id($tournament_id) {
		$sql = 'select a.tournament_id, a.match_id, a.match_date, a.match_scored, a.team_home as team_home_id, c.team_name as team_home_name, a.team_visit as team_visit_id, d.team_name as team_visit_name, b.score as home_score, e.score as visit_score from teams c, teams d, matches a left join ( select player_score.team_id, match_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by match_id, team_id ) b on ( a.match_id = b.match_id and a.team_home = b.team_id ) left join ( select player_score.team_id, match_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by match_id, team_id ) e on ( a.match_id = e.match_id and a.team_visit = e.team_id ) where c.team_id = a.team_home and d.team_id = a.team_visit and a.tournament_id = ' . $tournament_id . ' and a.match_scored = true order by a.match_date asc';
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	function get_matches_by_tournament_id_limit($tournament_id) {
		$sql = 'select a.tournament_id, a.match_id, a.match_date, a.match_scored, a.team_home as team_home_id, c.team_name as team_home_name, a.team_visit as team_visit_id, d.team_name as team_visit_name, b.score as home_score, e.score as visit_score from teams c, teams d, matches a left join ( select player_score.team_id, match_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by match_id, team_id ) b on ( a.match_id = b.match_id and a.team_home = b.team_id ) left join ( select player_score.team_id, match_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by match_id, team_id ) e on ( a.match_id = e.match_id and a.team_visit = e.team_id ) where c.team_id = a.team_home and d.team_id = a.team_visit and a.tournament_id = ' . $tournament_id . ' and a.match_scored = true order by a.match_date asc limit 10';
		$query = $this -> db -> query($sql);
		return $query -> result();
	}

	function get_team_statistics($team_id, $tournament) {

		$sql = 'select count(*) as PJ from matches where tournament_id = ' . $tournament . ' and match_scored = true and (team_home = ' . $team_id . ' or team_visit = ' . $team_id . ');';
		//'select count(*) as PJ from matches where team_home = ' . $team_id . ' or team_visit = ' . $team_id . ' and tournament_id = ' . $tournament . ' and match_scored = true;';

		$query = $this -> db -> query($sql);

		$results = $query -> result();

		$data = array();

		$data['sql_matches'] = $sql;

		if($results[0] -> PJ > 0) {

			$data['PJ'] = $results[0] -> PJ;

			$home_sql = 'SELECT a.match_id, a.team_home, a.team_visit, a.tournament_id, b.score as points_favor, c.score as points_against  from matches a left join (select player_score.match_id, player_score.team_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by team_id, match_id ) b on a.match_id = b.match_id and a.team_home = b.team_id left join (select player_score.match_id, player_score.team_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by team_id, match_id )c on a.team_visit = c.team_id and a.match_id = c.match_id where team_home=' . $team_id . ' and tournament_id = ' . $tournament . ' and match_scored = true;';
			$query_home = $this -> db -> query($home_sql);
			$results_home = $query_home -> result();

			$data['home_results'] = $results_home;

			$data['PG'] = 0;
			$data['PP'] = 0;
			$data['PG'] = 0;
			$data['PE'] = 0;
			$data['PP'] = 0;
			$data['GF'] = 0;
			$data['GC'] = 0;

			foreach($results_home as $row) {

				if(!isset($row -> points_favor)) {
					$row -> points_favor = 0;
					//$data['point_favor'] = 'not defined';
				}
				if(!isset($row -> points_against)) {
					$row -> points_against = 0;
					//$data['point_again'] = 'not defined';
				}

				if($row -> points_favor > $row -> points_against) {
					$data['PG']++;
				} elseif($row -> points_favor < $row -> points_against) {
					$data['PP']++;
				} elseif($row -> points_favor = $row -> points_against) {
					$data['PE']++;
				}

				$data['GF'] = $data['GF'] + $row -> points_favor;
				$data['GC'] = $data['GC'] + $row -> points_against;
				//$data['PG']
			}

			$visit_sql = 'SELECT a.match_id, a.team_home, a.team_visit, a.tournament_id, c.score as points_favor, b.score as points_against  from matches a left join (select player_score.match_id, player_score.team_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by team_id, match_id ) b on a.match_id = b.match_id and a.team_home = b.team_id left join (select player_score.match_id, player_score.team_id, sum(points) as score from player_score left join teams on teams.team_id = player_score.team_id group by team_id, match_id )c on a.team_visit = c.team_id and a.match_id = c.match_id where team_visit=' . $team_id . ' and tournament_id = ' . $tournament . ' and match_scored = true;';
			$query_visit = $this -> db -> query($visit_sql);
			$results_visit = $query_visit -> result();

			$data['visit_query'] = $visit_sql;
			$data['visit_results'] = $results_visit;

			foreach($results_visit as $row) {

				if(!isset($row -> points_favor)) {
					$row -> points_favor = 0;
					//$data['point_favor'] = 'not defined';
				}
				if(!isset($row -> points_against)) {
					$row -> points_against = 0;
					//$data['point_again'] = 'not defined';
				}

				if($row -> points_favor > $row -> points_against) {
					$data['PG']++;
				} elseif($row -> points_favor < $row -> points_against) {
					$data['PP']++;
				} elseif($row -> points_favor = $row -> points_against) {
					$data['PE']++;
				}

				$data['GF'] = $data['GF'] + $row -> points_favor;
				$data['GC'] = $data['GC'] + $row -> points_against;
				
				//$data['PG']
			}

			$data['dif'] = $data['GF'] - $data['GC'];
			//TODO use database rules to set up points value;
			$data['pts'] = ($data['PG'] * 3) + $data['PE'];
			return $data;

		} else {

			$data['PJ'] = 0;
			$data['PG'] = 0;
			$data['PE'] = 0;
			$data['PP'] = 0;
			$data['GF'] = 0;
			$data['GC'] = 0;
			$data['dif'] = $data['GF'] - $data['GC'];
			//TODO use database rules to set up points value;
			$data['pts'] = ($data['PG'] * 3) + $data['PE'];
		}

		return $data;
	}

}
?>